﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace SiteParser.Console.SitesConfig.SaksSite.DataEntities
{
	public class CatalogItemInsertParameters
	{
		public ushort AttributeSetId { get; set; }
		public ushort EntityTypeId { get; set; }
		public uint ParentId { get; set; }
		public int Level { get; set; }
		public string Label { get; set; }
		public string UrlId { get; set; }
		public int ChildrenCount { get; set; }
	}

	public class CatalogDataProvider
	{
		public uint InsertCatalogItem(MySqlConnection mySqlConnection, MageNtoDb magentoDb, CatalogItemInsertParameters insertParams) {
			var varCharEntity = (from o in magentoDb.CatalogCategoryEntityVArchAR
								 where o.EntityTypeID == DBConstants.CATEGORY_ENTITY_TYPE_ID
								&& o.AttributeID == DBConstants.CATEGORY_NAME_ATTRIBUTE_ID
								&& o.Value == insertParams.Label
								&& o.CatalogCategoryEntity.ParentID == insertParams.ParentId
								select o).FirstOrDefault();

			return varCharEntity != null ? varCharEntity.EntityID : AddCatalogItem(mySqlConnection, magentoDb, insertParams);
		}

		#region Private methods

		private uint AddCatalogItem(MySqlConnection mySqlConnection, MageNtoDb magentoDb, CatalogItemInsertParameters insertParams) {
			CatalogCategoryEntity parentCategory = GetCategory(magentoDb, insertParams.ParentId);

			if (parentCategory != null) {
				var categoryItem = new CatalogCategoryEntity() {
					AttributeSetID = insertParams.AttributeSetId,
					EntityTypeID = insertParams.EntityTypeId,
					ParentID = insertParams.ParentId,
					Level = parentCategory.Level + 1,
					Path = parentCategory.Path,
					ChildrenCount = insertParams.ChildrenCount,
					Position = 1
				};

				magentoDb.CatalogCategoryEntities.InsertOnSubmit(categoryItem);
				magentoDb.SubmitChanges();

				categoryItem.Path = string.Format("{0}/{1}", categoryItem.Path, categoryItem.EntityID);

				var catalogVarCharEntity = new CatalogCategoryEntityVArchAR()
				{
					AttributeID = DBConstants.CATEGORY_NAME_ATTRIBUTE_ID,
					EntityTypeID = insertParams.EntityTypeId,
					EntityID = categoryItem.EntityID,
					StoreID = DBConstants.DEFAULT_STORE_ID,
					Value = insertParams.Label
				};
				InsertCategoryVarchar(mySqlConnection, catalogVarCharEntity);

				//Make category active
				var entityInt = new CatalogCategoryEntityInt() {
					AttributeID = DBConstants.CATEGORY_IS_ACTIVE_ATTRIBUTE_ID,
					EntityID = categoryItem.EntityID,
					EntityTypeID = categoryItem.EntityTypeID,
					Value = DBConstants.CATEGORY_ACTIVE_VALUE,
					StoreID = DBConstants.DEFAULT_STORE_ID
				};
				ActivateCategory(mySqlConnection, entityInt);

				magentoDb.SubmitChanges();

				return categoryItem.EntityID;
			}

			return 0;
		}

		private CatalogCategoryEntity GetCategory(MageNtoDb magentoDb, uint categoryId) {
			return magentoDb.CatalogCategoryEntities.Where(x => x.EntityID == categoryId).FirstOrDefault();
		}

		private void InsertCategoryVarchar(MySqlConnection mySqlConnection, CatalogCategoryEntityVArchAR varcharEntity) {
			if (mySqlConnection != null) {
				if (mySqlConnection.State != System.Data.ConnectionState.Open) {
					mySqlConnection.Open();
				}

				MySqlCommand sqlCommand = new MySqlCommand();
				sqlCommand.Connection = mySqlConnection;
				sqlCommand.CommandType = System.Data.CommandType.Text;
				sqlCommand.CommandText = string.Format(string.Join(string.Empty, new string[]{
					"INSERT INTO catalog_category_entity_varchar ",
					"(entity_type_id, attribute_id, store_id, entity_id, value)",
					"VALUES ({0},{1},{2},{3},\"{4}\")"}), varcharEntity.EntityTypeID, varcharEntity.AttributeID,
					varcharEntity.StoreID, varcharEntity.EntityID, varcharEntity.Value);

				sqlCommand.ExecuteNonQuery();
			}
		}

		private void ActivateCategory(MySqlConnection mySqlConnection, CatalogCategoryEntityInt activateEntity) {
			if (mySqlConnection != null) {
				if (mySqlConnection.State != System.Data.ConnectionState.Open) {
					mySqlConnection.Open();
				}

				MySqlCommand sqlCommand = new MySqlCommand();
				sqlCommand.Connection = mySqlConnection;
				sqlCommand.CommandType = System.Data.CommandType.Text;
				sqlCommand.CommandText = string.Format(string.Join(string.Empty, new string[]{
					"INSERT INTO catalog_category_entity_int ",
					"(entity_type_id, attribute_id, store_id, entity_id, value)",
					"VALUES ({0},{1},{2},{3},'{4}')"}), activateEntity.EntityTypeID, activateEntity.AttributeID,
					activateEntity.StoreID, activateEntity.EntityID, activateEntity.Value);

				sqlCommand.ExecuteNonQuery();
			}
		}

		#endregion Private methods
	}
}
